package com.xxxy.jsq.dao;

import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Page;
import com.xxxy.jsq.model.Role;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RoleDao implements BaseDao<Role> {
    private C3P0Utils db = new C3P0Utils();
    public RoleDao() {
        this.db = new C3P0Utils();
    }

    @Override
    public String add(Role model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("insert into pa_role(role_name, role_flag) ");
        sqlStr.append("values(?,?) ");

        conn = db.getConnection();
        // 检查角色名称是否重复
        boolean flag = isSame(model, true);
        if (!flag) {
            int num = 0;
            try {
                pstm = conn.prepareStatement(sqlStr.toString());
                pstm.setString(1, model.getRoleName());
                pstm.setString(2, model.getRoleFlag());
                num = pstm.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return num > 0 ? "Ok" : "Err";
        } else {
            return "Same";
        }
    }

    @Override
    public String update(Role model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();

        // 检查角色名称是否重复（排除自身）
        if (isSame(model, false)) {
            return "Same";
        }

        sqlStr.append("update pa_role set ");
        if (model.getRoleName() != null && !model.getRoleName().equals("")) {
            sqlStr.append(" role_name='" + model.getRoleName() + "'");
        }
        if (model.getRoleFlag() != null && !model.getRoleFlag().equals("")) {
            sqlStr.append(" ,role_flag='" + model.getRoleFlag() + "'");
        }
        sqlStr.append(" where role_id=?");

        int num = 0;
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, model.getRoleId());
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        return num > 0 ? "Ok" : "Err";
    }


    @Override
    public String delete(Role model) {
        return "";
    }

    @Override
    public String deleteById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("delete from pa_role where role_id=? ");

        conn = db.getConnection();
        int num = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        return num > 0 ? "Ok" : "Err";
    }

    @Override
    public List<Role> getAllData(Role model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Role> list = new ArrayList<Role>();

        sqlStr.append(" SELECT * from pa_role ");
        sqlStr.append("where 1=1 ");
        if (model.getRoleName() != null && !model.getRoleName().equals("")) {
            sqlStr.append("and role_name='" + model.getRoleName() + "' ");
        }
        if (model.getRoleFlag() != null && !model.getRoleFlag().equals("")) {
            sqlStr.append(" and role_flag='" + model.getRoleFlag() + "' ");
        }
        sqlStr.append(" ORDER BY role_id asc, role_name asc ");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Role role = new Role();
                role.setRoleId(rs.getLong("role_id"));
                role.setRoleName(rs.getString("role_name"));
                role.setRoleFlag(rs.getString("role_flag").equals("0") ? "启用" : "禁用");
                list.add(role);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Role findById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" SELECT * from pa_role ");
        sqlStr.append("where 1=1 and role_id=? ");

        conn = db.getConnection();
        Role role = new Role();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            rs = pstm.executeQuery();
            while (rs.next()) {
                role.setRoleId(rs.getLong("role_id"));
                role.setRoleName(rs.getString("role_name"));
                role.setRoleFlag(rs.getString("role_flag").equals("0") ? "启用" : "禁用");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return role;
    }

    @Override
    public Role find(Role model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" SELECT * from pa_role ");
        sqlStr.append("where 1=1 ");

        if (model.getRoleName() != null && !model.getRoleName().equals("")) {
            sqlStr.append("and role_name='" + model.getRoleName() + "' ");
        }
        if (model.getRoleFlag() != null && !model.getRoleFlag().equals("")) {
            sqlStr.append(" and role_flag='" + model.getRoleFlag() + "' ");
        }

        conn = db.getConnection();
        Role role = new Role();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                role.setRoleId(rs.getLong("role_id"));
                role.setRoleName(rs.getString("role_name"));
                role.setRoleFlag(rs.getString("role_flag").equals("0") ? "启用" : "禁用");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return role;
    }

    @Override
    public boolean isSame(Role model, boolean sflag) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" select * from pa_role where role_name=? ");

        // 更新时排除自身ID
        if (!sflag) {
            sqlStr.append("and role_id!='" + model.getRoleId() + "' ");
        }

        conn = db.getConnection();
        boolean flag = false;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setString(1, model.getRoleName());
            rs = pstm.executeQuery();
            flag = rs.next(); // 存在同名角色则返回true
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public List<Role> getAllDataByPage(Role model, Page page) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Role> list = new ArrayList<Role>();

        sqlStr.append(" SELECT * from pa_role ");
        sqlStr.append("where 1=1 ");
        if (model.getRoleName() != null && !model.getRoleName().equals("")) {
            sqlStr.append("and role_name like '%" + model.getRoleName() + "%' ");
        }
        if (model.getRoleFlag() != null && !model.getRoleFlag().equals("")) {
            sqlStr.append(" and role_flag='" + model.getRoleFlag() + "' ");
        }

        sqlStr.append(" ORDER BY role_id asc, role_name asc ");
        sqlStr.append(" limit ?,? ");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setInt(1, (page.getCurrentPage() - 1) * page.getCount());
            pstm.setInt(2, page.getCount());
            rs = pstm.executeQuery();

            while (rs.next()) {
                Role role = new Role();
                role.setRoleId(rs.getLong("role_id"));
                role.setRoleName(rs.getString("role_name"));
                role.setRoleFlag(rs.getString("role_flag").equals("0") ? "启用" : "禁用");
                list.add(role);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(Role model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("select count(*) from pa_role ");
        sqlStr.append("where 1=1 ");

        if (model.getRoleName() != null && !model.getRoleName().equals("")) {
            sqlStr.append("and role_name='" + model.getRoleName() + "' ");
        }
        if (model.getRoleFlag() != null && !model.getRoleFlag().equals("")) {
            sqlStr.append(" and role_flag='" + model.getRoleFlag() + "' ");
        }

        conn = db.getConnection();
        int total = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    @Override
    public List<Role> getAllky() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Role> list = new ArrayList<Role>();

        sqlStr.append("select * from pa_role where role_flag='0' "); // 0表示启用
        sqlStr.append("order by role_name asc ");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Role role = new Role();
                role.setRoleId(rs.getLong("role_id"));
                role.setRoleName(rs.getString("role_name"));
                list.add(role);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

}
